package com.cnsugar.common.jdbc.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cnsugar.common.jdbc.model.BatchModel;
import com.cnsugar.common.jdbc.model.SqlModel;
import com.cnsugar.common.jdbc.model.SaveOrUpdateModel;
import com.cnsugar.common.reflect.BeanField;
import com.cnsugar.common.reflect.ReflectUtils;
import com.cnsugar.common.jdbc.DbException;
import com.cnsugar.common.jdbc.annotation.Table;
import com.cnsugar.common.jdbc.model.FieldType;
import com.cnsugar.common.utils.CamelCaseUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Date;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * jdbc辅助工具类
 *
 * @Author Sugar
 * @Version 2018/7/23 17:35
 */
public class JdbcUtils {
    protected static Logger log = LoggerFactory.getLogger(JdbcUtils.class);
    protected static HashMap<String, String> tableMap = new HashMap<>();
    public static boolean columnUpper = "true".equalsIgnoreCase(System.getProperty("jdbc.column.upper", "true"));
    public static boolean tableUpper = "true".equalsIgnoreCase(System.getProperty("jdbc.table.upper", "true"));
    /**
     * 根据类获取对应的表名
     *
     * @param clazz
     * @return
     */
    public static String getTableName(Class<?> clazz) {
        String name = clazz.getName();
        String tableName = tableMap.get(name);
        if (tableName != null) {
            return tableName;
        }

        Table tb = clazz.getAnnotation(Table.class);
        if (tb != null) {
            if (tb.value() != null && !tb.value().isEmpty()) {
                tableName = tb.value();
            } else {
                tableName = CamelCaseUtils.toUnderlineName(clazz.getSimpleName(), tableUpper);
            }
        } else {
            tableName = CamelCaseUtils.toUnderlineName(clazz.getSimpleName(), tableUpper);
        }
        tableMap.put(name, tableName);
        return tableName;
    }

    /**
     * 生成对象的更新模型
     *
     * @param t            对象
     * @param limitColumns 需要更新或忽略的字段
     * @param ignore       true-忽略
     * @return
     * @throws SecurityException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static <T> SqlModel buildUpdateModel(T t, String[] limitColumns, boolean ignore, String mark) throws IllegalAccessException, IllegalArgumentException,
            InvocationTargetException,
            NoSuchMethodException, SecurityException {
        StringBuilder sql_set = new StringBuilder();
        String fieldName;
        Class<?> fieldType;
        Class<?> clazz = t.getClass();
        String table = getTableName(clazz);
        List<BeanField> fields = ReflectUtils.getBeanFields(clazz);
        int length = fields.size();
        Object[] values = new Object[length];
        int[] types = new int[length];
        Object value = null;
        int i = 0;
        String column = null;
        com.cnsugar.common.jdbc.annotation.Field annotation = null;
        String pkColumn = null;
        Object pkValue = null;
        Method method;

        for (BeanField field : fields) {
            fieldName = field.getName();
            column = CamelCaseUtils.toUnderlineName(fieldName, columnUpper);
            annotation = field.getAnnotation(com.cnsugar.common.jdbc.annotation.Field.class);
            if (annotation != null) {
                if (!annotation.isColumn() || annotation.readOnly()) {
                    continue;
                }
                if (annotation.value() != null && !annotation.value().isEmpty()) {
                    column = annotation.value();
                }
                if (annotation.isPK()) {
                    Method pkMethod = field.getGetterMethod();
                    if (pkMethod == null) {
                        throw new DbException("No getter method: " + fieldName);
                    }
                    pkValue = pkMethod.invoke(t);
                    pkColumn = column;
                    continue;
                }
            }
            if (ignore) {// 忽略指定的字段
                if (limitColumns != null && ArrayUtils.isInArray(column, limitColumns)) {
                    continue;
                }
            } else {
                if (limitColumns != null && !ArrayUtils.isInArray(column, limitColumns)) {
                    continue;
                }
            }
            method = field.getGetterMethod();
            fieldType = method.getReturnType();
            if (annotation != null && annotation.isJson()) {
                value = method.invoke(t);
                if (fieldType.isAssignableFrom(List.class)) {
                    value = JSONArray.toJSONString(value);
                } else {
                    value = JSONObject.toJSONString(value);
                }
                types[i] = Types.LONGVARCHAR;
            } else {
                Integer type = toSqlType(fieldType);
                if (type == null) {
                    if (log.isDebugEnabled()) {
                        log.debug("Attribute type is not supported: name=" + fieldName + "; type=" + fieldType.getName());
                    }
                    continue;
                }
                types[i] = type;
                value = method.invoke(t);
            }
            if (value != null || (annotation != null && !annotation.ignoreNull())) {//字段为空不更新
                sql_set.append(",").append(mark).append(column).append(mark).append("=?");
                values[i] = value;
                i++;
            }
        }
        StringBuilder sql = new StringBuilder("UPDATE ");
        sql.append(" ").append(table);
        sql.append(" SET ").append(sql_set.substring(1));
        sql.append(" WHERE ").append(pkColumn).append("=?");

        Object[] newValues = new Object[i + 1];
        newValues[i] = pkValue;
        for (int j = 0; j < i; j++) {
            newValues[j] = values[j];
        }
        values = newValues;
        int[] newTypes = new int[i + 1];
        newTypes[i] = Types.VARCHAR;
        for (int j = 0; j < i; j++) {
            newTypes[j] = types[j];
        }
        types = newTypes;

        return new SqlModel(sql.toString(), values, types, pkColumn);
    }

    /**
     * 新增或更新对象模型
     *
     * @param t
     * @return
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     * @throws InvocationTargetException
     */
    public static SaveOrUpdateModel buildSaveOrUpdateModel(Object t) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException {
        String fieldName;
        Class<?> clazz = t.getClass();
        Class<?> fieldType;
        String table = getTableName(clazz);
        List<BeanField> fields = ReflectUtils.getBeanFields(clazz);
        List<String> columns = new ArrayList<>();
        List<Object> values = new ArrayList<>();
        List<Integer> types = new ArrayList<>();
        Object value = null;
        String column = null;
        com.cnsugar.common.jdbc.annotation.Field annotation = null;
        String pkName = null;
        Object pkValue = null;
        for (BeanField field : fields) {
            fieldName = field.getName();
            annotation = field.getAnnotation(com.cnsugar.common.jdbc.annotation.Field.class);
            if (annotation != null) {
                if (!annotation.isColumn() || annotation.readOnly()) {
                    continue;
                }
                if (annotation.value() != null && !annotation.value().isEmpty()) {
                    column = annotation.value();
                } else {
                    column = CamelCaseUtils.toUnderlineName(fieldName, columnUpper);
                }
            } else {
                column = CamelCaseUtils.toUnderlineName(fieldName, columnUpper);
            }
            Method method = field.getGetterMethod();
            if (method == null) {
                continue;
            }
            fieldType = method.getReturnType();
            if (annotation != null && annotation.isJson()) {
                value = method.invoke(t);
                if (fieldType.isAssignableFrom(List.class)) {
                    value = JSONArray.toJSONString(value);
                } else {
                    value = JSONObject.toJSONString(value);
                }
                types.add(Types.LONGVARCHAR);
            } else {
                Integer type = toSqlType(fieldType);
                if (type == null) {
                    if (log.isDebugEnabled()) {
                        log.debug("Attribute type is not supported: name=" + fieldName + "; type=" + fieldType.getName());
                    }
                    continue;
                }
                types.add(type);
                value = method.invoke(t);
                if (annotation != null) {
                    if (annotation.isPK()) {
                        pkValue = value;
                        pkName = column;
                    }
                    if (annotation.Seq() != null && !annotation.Seq().isEmpty()) {
                        if (value == null || "".equals(value + "") || "0".equals(value)) {
                            value = annotation.Seq() + ".NEXTVAL";
                        }
                    }
                }
            }
            columns.add(column);
            values.add(value);
        }
        return new SaveOrUpdateModel(table, pkName, pkValue, columns, values, types);
    }

    /**
     * 生成批量新增对象模型
     *
     * @param list
     * @return
     * @throws SecurityException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static <T> BatchModel buildBatchSaveModel(List<T> list, String mark) throws IllegalAccessException, IllegalArgumentException,
            InvocationTargetException,
            NoSuchMethodException,
            SecurityException {
        StringBuilder sql_fields = new StringBuilder();
        StringBuilder sql_values = new StringBuilder();

        String fieldName;
        Class<?> fieldType;
        int i = 0;
        T t = list.get(0);
        Class<?> clazz = t.getClass();
        String table = getTableName(clazz);
        List<BeanField> fields = ReflectUtils.getBeanFields(clazz);
        int length = fields.size();
        Method[] methods = new Method[length];
        int[] types = new int[length];
        FieldType[] valueFlag = new FieldType[length];
        String column = null;
        com.cnsugar.common.jdbc.annotation.Field annotation = null;
        for (BeanField field : fields) {
            fieldName = field.getName();
            annotation = field.getAnnotation(com.cnsugar.common.jdbc.annotation.Field.class);
            if (annotation != null) {
                if (!annotation.isColumn() || annotation.readOnly()) {
                    continue;
                }
                if (annotation.value() != null && !annotation.value().isEmpty()) {
                    column = annotation.value();
                } else {
                    column = CamelCaseUtils.toUnderlineName(fieldName, columnUpper);
                }
                if (annotation.Seq() != null && !annotation.Seq().isEmpty()) {//使用序列
                    sql_fields.append(",").append(mark).append(column).append(mark);
                    sql_values.append(",").append(annotation.Seq()).append(".NEXTVAL");
                    continue;
                }
            } else {
                column = CamelCaseUtils.toUnderlineName(fieldName, columnUpper);
            }
            fieldType = field.getType();
            if (!toSqlType(annotation, fieldType, types, valueFlag, i)) {
                if (log.isDebugEnabled()) {
                    log.debug("Attribute type is not supported: name=" + fieldName + "; type=" + fieldType.getName());
                }
                continue;
            }
            methods[i] = field.getGetterMethod();
            if (methods[i] == null) {
                continue;
            }
            sql_fields.append(",").append(mark).append(column).append(mark);
            sql_values.append(",?");
            i++;
        }

        final StringBuilder sql = new StringBuilder("INSERT INTO");
        sql.append(" ").append(table);
        sql.append(" (").append(sql_fields.substring(1)).append(")");
        sql.append(" VALUES (").append(sql_values.substring(1)).append(")");

        if (i != length) {
            types = ArrayUtils.subArray(types, i);
        }
        List<Object[]> valueList = new ArrayList<>();
        for (Object obj : list) {
            Object[] values = new Object[i];
            setValues(values, valueFlag, obj, i, methods);
            valueList.add(values);
        }

        return new BatchModel(sql.toString(), valueList, types);
    }

    /**
     * 生成新增对象模型
     *
     * @param t
     * @return
     * @throws SecurityException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static <T> SqlModel buildSaveModel(T t, String mark) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException,
            NoSuchMethodException,
            SecurityException {
        StringBuilder sql_fields = new StringBuilder();
        StringBuilder sql_values = new StringBuilder();
        String fieldName;
        Object value = null;
        int i = 0;
        Class<?> clazz = t.getClass();
        Class<?> fieldType;
        String table = getTableName(clazz);
        List<BeanField> fields = ReflectUtils.getBeanFields(clazz);
        int length = fields.size();
        Object[] values = new Object[length];
        int[] types = new int[length];
        String column = null;
        com.cnsugar.common.jdbc.annotation.Field annotation = null;
        String pk = null;
        Method method;
        for (BeanField field : fields) {
            fieldName = field.getName();
            annotation = field.getAnnotation(com.cnsugar.common.jdbc.annotation.Field.class);
            if (annotation != null) {
                if (!annotation.isColumn() || annotation.readOnly()) {
                    continue;
                }
                if (annotation.value() != null && !"".equals(annotation.value())) {
                    column = annotation.value();
                } else {
                    column = CamelCaseUtils.toUnderlineName(fieldName, columnUpper);
                }
                if (annotation.isPK()) {
                    pk = column;
                }
                if (annotation.Seq() != null && !"".equals(annotation.Seq())) {
                    sql_fields.append(",").append(mark).append(column).append(mark);
                    sql_values.append(",").append(annotation.Seq()).append(".NEXTVAL");
                    continue;
                }
            } else {
                column = CamelCaseUtils.toUnderlineName(fieldName, columnUpper);
            }
            method = field.getGetterMethod();
            if (method == null) {
                continue;
            }
            fieldType = method.getReturnType();
            if (annotation != null && annotation.isJson()) {
                value = method.invoke(t);
                if (value == null) {
                    continue;
                }
                if (fieldType.isAssignableFrom(List.class)) {
                    value = JSONArray.toJSONString(value);
                } else {
                    value = JSONObject.toJSONString(value);
                }
                types[i] = Types.LONGVARCHAR;
            } else {
                Integer type = toSqlType(fieldType);
                if (type == null) {
                    if (log.isDebugEnabled()) {
                        log.debug("Attribute type is not supported: name=" + fieldName + "; type=" + fieldType.getName());
                    }
                    continue;
                }
                types[i] = type;
                value = method.invoke(t);
                if (value == null) {
                    continue;
                }
            }
            sql_fields.append(",").append(mark).append(column).append(mark);
            sql_values.append(",?");
            values[i] = value;
            i++;
        }

        final StringBuilder sql = new StringBuilder("INSERT INTO");
        sql.append(" ").append(table);
        sql.append(" (").append(sql_fields.substring(1)).append(")");
        sql.append(" VALUES (").append(sql_values.substring(1)).append(")");

        if (i != length) {
            types = ArrayUtils.subArray(types, i);
            values = ArrayUtils.subArray(values, i);
        }

        return new SqlModel(sql.toString(), values, types, pk);
    }

    /**
     * 生成批量更新对象模型
     *
     * @param list
     * @param limitColumns 指定要更新的字段名(数据库表中的字段)
     * @param ignore       true-忽略
     * @return
     * @throws SecurityException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static <T> BatchModel buildBatchUpdateModel(List<T> list, String[] limitColumns, boolean ignore, String mark) throws IllegalAccessException,
            IllegalArgumentException,
            InvocationTargetException, NoSuchMethodException, SecurityException {
        StringBuilder sql_set = new StringBuilder();
        String fieldName;
        Class<?> fieldType;
        Class<?> clazz = list.get(0).getClass();
        String table = getTableName(clazz);
        List<BeanField> fields = ReflectUtils.getBeanFields(clazz);
        int length = fields.size();
        Method[] methods = new Method[length];
        int[] types = new int[length];
        FieldType[] valueFlag = new FieldType[length];
        int i = 0;
        String column = null;
        com.cnsugar.common.jdbc.annotation.Field annotation = null;
        Method pkMethod = null;
        String pkColumn = null;
        for (BeanField field : fields) {
            fieldName = field.getName();
            column = CamelCaseUtils.toUnderlineName(fieldName, columnUpper);
            annotation = field.getAnnotation(com.cnsugar.common.jdbc.annotation.Field.class);
            if (annotation != null) {
                if (!annotation.isColumn() || annotation.readOnly()) {
                    continue;
                }
                if (annotation.value() != null && !annotation.value().isEmpty()) {
                    column = annotation.value();
                }
                if (annotation.isPK()) {
                    pkMethod = field.getGetterMethod();
                    if (pkMethod == null) {
                        throw new DbException("No getter method: " + fieldName);
                    }
                    pkColumn = column;
                    continue;
                }
            }
            if (ignore) {// 忽略指定的属性
                if (limitColumns != null && ArrayUtils.isInArray(column, limitColumns)) {
                    continue;
                }
            } else {
                if (limitColumns != null && !ArrayUtils.isInArray(column, limitColumns)) {
                    continue;
                }
            }
            fieldType = field.getType();
            if (!toSqlType(annotation, fieldType, types, valueFlag, i)) {
                if (log.isDebugEnabled()) {
                    log.debug("Attribute type is not supported: name=" + fieldName + "; type=" + fieldType.getName());
                }
                continue;
            }
            methods[i] = field.getGetterMethod();
            if (methods[i] == null) {
                continue;
            }
            sql_set.append(",").append(mark).append(column).append(mark).append("=?");
            i++;
        }
        StringBuilder sql = new StringBuilder("UPDATE ");
        sql.append(" ").append(table);
        sql.append(" SET ").append(sql_set.substring(1));
        sql.append(" WHERE ").append(pkColumn).append("=?");

        int[] newTypes = new int[i + 1];
        newTypes[i] = Types.VARCHAR;
        for (int j = 0; j < i; j++) {
            newTypes[j] = types[j];
        }
        types = newTypes;

        List<Object[]> valueList = new ArrayList<>();
        for (Object obj : list) {
            Object[] values = new Object[i + 1];
            setValues(values, valueFlag, obj, i, methods);
            values[i] = pkMethod.invoke(obj);
            valueList.add(values);
        }

        return new BatchModel(sql.toString(), valueList, types);
    }

    /**
     * 通过反射读取值
     *
     * @param values
     * @param valueFlag
     * @param obj
     * @param size
     * @param methods
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    private static final void setValues(Object[] values, FieldType[] valueFlag, Object obj, int size, Method[] methods) throws InvocationTargetException,
            IllegalAccessException {
        for (int j = 0; j < size; j++) {
            Object value = methods[j].invoke(obj);
            if (valueFlag[j] == FieldType.JSON_ARRAY) {
                values[j] = JSONArray.toJSONString(value);
            } else if (valueFlag[j] == FieldType.JSON_OBJECT) {
                values[j] = JSONObject.toJSONString(value);
            } else {
                values[j] = value;
            }
        }
    }

    /**
     * 将java类型转换成sql类型
     *
     * @param fieldType
     * @return
     */
    private static final Integer toSqlType(Class fieldType) {
        if (fieldType == String.class) {
            return Types.VARCHAR;
        } else if (fieldType == Integer.class || fieldType == int.class) {
            return Types.INTEGER;
        } else if (fieldType == Long.class || fieldType == long.class) {
            return Types.BIGINT;
        } else if (fieldType == Float.class || fieldType == float.class) {
            return Types.FLOAT;
        } else if (fieldType == Double.class || fieldType == double.class) {
            return Types.DOUBLE;
        } else if (fieldType == java.util.Date.class) {
            return Types.TIMESTAMP;
        } else if (fieldType == byte[].class) {
            return Types.BLOB;
        } else if (fieldType == Short.class || fieldType == short.class) {
            return Types.SMALLINT;
        } else if (fieldType == Timestamp.class) {
            return Types.TIMESTAMP;
        } else if (fieldType == Date.class) {
            return Types.DATE;
        } else if (fieldType == Byte.class) {
            return Types.TINYINT;
        }
        return null;
    }

    private static final boolean toSqlType(com.cnsugar.common.jdbc.annotation.Field annotation, Class fieldType, int[] types, FieldType[]
            valueFlag, int i) {
        if (annotation != null && annotation.isJson()) {
            if (fieldType.isAssignableFrom(List.class) || fieldType.isArray()) {
                valueFlag[i] = FieldType.JSON_ARRAY;
            } else {
                valueFlag[i] = FieldType.JSON_OBJECT;
            }
            types[i] = Types.LONGVARCHAR;
        } else {
            Integer type = toSqlType(fieldType);
            if (type == null) {
                return false;
            }
            types[i] = type;
        }
        return true;
    }

    /**
     * 拆解简单sql type:1-取from，2-取where
     */
    public static String parseHql(String sql, int type) {
        int where = sql.indexOf(" WHERE ");
        int order = sql.indexOf(" ORDER BY ");
        switch (type) {
            case 1:
                if (where > 0) {
                    return sql.substring(0, where);
                } else if (order > 0) {
                    return sql.substring(0, order);
                } else {
                    return sql;
                }
            case 2:
                if (where > 0) {
                    if (order > 0) {
                        return sql.substring(where, order);
                    } else {
                        return sql.substring(where);
                    }
                } else {
                    return null;
                }
        }

        return null;
    }
}
